﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CommonLibrary.DB;
using CommonLibrary.DataModel.Basic;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class CledDao
    {
        public String GetOutstandDates(String year, String month, Int32 dayCount, String userId)
        {
            String outstandDates = String.Empty;
            String minDate = year + "-" + month + "-" + "1";
            String maxDate = year + "-" + month + "-" + dayCount;

            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@maxDate", maxDate));
            parameterList.Add(new SqlParameter("@minDate", minDate));

            String sql = "SELECT DISTINCT [note_time],[note_type] FROM system_calen WHERE [user_id]=@userId " +
                "AND CONVERT(datetime,[note_time]) >= CONVERT(datetime,@minDate) " +
                "AND CONVERT(datetime,[note_time]) <= CONVERT(datetime,@maxDate)";

            var notes = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());

            var noteResult = String.Empty;
            if (notes != null)
            {
                foreach (var note in notes)
                {
                    if (note[1] == "NORMAL")
                        noteResult += System.Text.RegularExpressions.Regex.Match(note[0], @"\d*$").ToString() + ";";
                }
                noteResult += "-";

                foreach (var note in notes)
                {
                    if (note[1] == "HURRY")
                        noteResult += System.Text.RegularExpressions.Regex.Match(note[0], @"\d*$").ToString() + ";";
                }
                noteResult += "-";

                foreach (var note in notes)
                {
                    noteResult += System.Text.RegularExpressions.Regex.Match(note[0], @"\d*$").ToString() + ";";
                }
            }
            return noteResult;
        }

        /// <summary>
        ///  根据日期获得笔记
        /// </summary>
        /// <param name="date"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public String[][] GetNotes(String date, String userId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@date", date));
            parameterList.Add(new SqlParameter("@userId", userId));

            String sql = "SELECT [id],[note_text],[text_msg],[note_time],[msg_time],[note_type] FROM system_calen " +
                "WHERE [user_id]=@userId AND [note_time]=@date";

            return DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
        }

        public DataTable GetNotesUtilCurrDay(String date, String userId)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@date", date));
            parameterList.Add(new SqlParameter("@userId", userId));

            String sql = " SELECT [id],[note_text],[text_msg],[note_time],[msg_time],[note_type],[user_name] FROM system_calen c,jc_user u " +
                              " WHERE c.[user_id]=@userId and c.[user_id]=u.[user_id] AND CONVERT(datetime,CONVERT(VARCHAR(10),[note_time],120)) <= CONVERT(datetime,CONVERT(VARCHAR(10),@date,120)) " +
                              " AND [note_type] != 'READED' ";

            return DBExecuteUtil.querySqlTable(sql, parameterList.ToArray());
        }

        public void AddNote(CledEntity cledEntity, String userId)
        {
            String sql;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@text", cledEntity.noteText));
            parameterList.Add(new SqlParameter("@date", cledEntity.noteTime));
            parameterList.Add(new SqlParameter("@textMsg", cledEntity.textMsg.ToString()));
            parameterList.Add(new SqlParameter("@userId", userId));
            parameterList.Add(new SqlParameter("@msgTime", cledEntity.msgTime));
            parameterList.Add(new SqlParameter("@noteType", cledEntity.noteType));
            sql = "INSERT INTO system_calen ([note_text],[note_time],[text_msg],[user_id],[msg_time],[note_type]) " +
                "VALUES (@text,@date,@textMsg,@userId,@msgTime,@noteType)";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateNote(CledEntity cledEntity)
        {
            String sql;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@text", cledEntity.noteText));
            parameterList.Add(new SqlParameter("@textMsg", cledEntity.textMsg.ToString()));
            parameterList.Add(new SqlParameter("@noteId", cledEntity.id));
            parameterList.Add(new SqlParameter("@noteType", cledEntity.noteType));
            sql = "UPDATE system_calen SET [note_text]=@text,[text_msg]=@textMsg,[note_type]=@noteType WHERE [id]=@noteId";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void DeleteNote(String noteId)
        {
            String sql;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@noteId", noteId));
            sql = "DELETE FROM system_calen WHERE [id]=@noteId";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void ReadNote(String noteId)
        {
            String sql;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@noteId", noteId));
            sql = "UPDATE system_calen SET [note_type]='READED' WHERE [id]=@noteId";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        //private String monthNum(String month)
        //{
        //    switch (month)
        //    {
        //        case "January":
        //            return "1";
        //        case "February":
        //            return "2";
        //        case "March":
        //            return "3";
        //        case "April":
        //            return "4";
        //        case "May":
        //            return "5";
        //        case "June":
        //            return "6";
        //        case "July":
        //            return "7";
        //        case "August":
        //            return "8";
        //        case "September":
        //            return "9";
        //        case "October":
        //            return "10";
        //        case "November":
        //            return "11";
        //        case "December":
        //            return "12";
        //        default:
        //            return "";
        //    }
        //}
    }
}
